When creating a correlated sub-query, it's easier to specify the predicates through a specific method, based on a generated relation. This helps avoiding memorizing which FK fields tie which entities together. Correlation predicates can be specified directly using the .Where() extension method of a query object as with any other predicate for the query or by using the method .CorrelatedOver(predicate). To specify a specific correlation relation for a subquery, use the method .CorrelatedOver(relation).
The CorrelatedOver(relation) method has some overloads to specify aliases for start and/or end entity. The relation specified in the overloads which accept an EntityRelation is used to produce the predicate to correlate the two queries, based on the fk/pk fields in the relationship.
The correlation predicate (either specified directly or constructed from the relation specified) will be appended to the Where clause specified with And as a normal predicate
CorrelatedOver(relation) is only available on an EntityQuery instance.
The following example will fetch a list of order collections based on a query on the related customer entity: per customer matching the outer query a query on the orders is executed which is correlated to the outer query.
var qf = new QueryFactory(); var q = qf.Customer .Where(CustomerFields.CustomerId.NotIn(new List<string>() { "FISSA", "PARIS" })) .Select(() => qf.Order .CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId) .ToResultset() ); // generated SQL: SELECT [Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId], [Northwind].[dbo].[Orders].[EmployeeID] AS [EmployeeId], [Northwind].[dbo].[Orders].[Freight], [Northwind].[dbo].[Orders].[OrderDate], [Northwind].[dbo].[Orders].[OrderID] AS [OrderId], [Northwind].[dbo].[Orders].[RequiredDate], [Northwind].[dbo].[Orders].[ShipAddress], [Northwind].[dbo].[Orders].[ShipCity], [Northwind].[dbo].[Orders].[ShipCountry], [Northwind].[dbo].[Orders].[ShipName], [Northwind].[dbo].[Orders].[ShippedDate], [Northwind].[dbo].[Orders].[ShipPostalCode], [Northwind].[dbo].[Orders].[ShipRegion], [Northwind].[dbo].[Orders].[ShipVia] FROM [Northwind].[dbo].[Orders] WHERE ((((EXISTS (SELECT [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId] FROM [Northwind].[dbo].[Customers] WHERE (((([Northwind].[dbo].[Customers].[CustomerID] NOT IN ('FISSA' /* @p1 */, 'PARIS' /* @p2 */)))) AND [Northwind].[dbo].[Orders].[CustomerID] = [Northwind].[dbo].[Customers].[CustomerID]))))))